package com.ki.glasscase.svc.dao.impl;

import com.ki.glasscase.svc.dao.ProductDAO;
import com.ki.glasscase.svc.entity.Product;
import com.ki.glasscase.svc.entity.Tag;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.dao.DataIntegrityViolationException;
import org.springframework.dao.DuplicateKeyException;
import org.springframework.jdbc.core.*;
import org.springframework.jdbc.support.GeneratedKeyHolder;
import org.springframework.jdbc.support.KeyHolder;
import org.springframework.stereotype.Repository;
import javax.sql.DataSource;
import java.sql.*;
import java.util.List;
import java.util.logging.Logger;


/**
 * Created by Khacheresyants.L on 2/13/14.
 */
@Repository
public class ImplProductDAO implements ProductDAO {

    private Logger logger = Logger.getLogger("ImplProductDAO.class");

    private JdbcTemplate jdbcTemplate;

    @Autowired
    public void setDataSource(DataSource dataSource) {
        jdbcTemplate = new JdbcTemplate(dataSource);
    }

    @Override
    public Long create(final Product product) {


        final String sqlQuery = "INSERT INTO product (original_id, name, translate_name, description, price, old_price, shop_name," +
                " img_url, buy_url, available, created) VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, CURRENT_TIMESTAMP);";

        logger.info("Create product");
        Long id = null;
        try {
            KeyHolder keyHolder = new GeneratedKeyHolder();
            jdbcTemplate.update(new PreparedStatementCreator() {
                @Override
                public PreparedStatement createPreparedStatement(Connection con) throws SQLException {
                    PreparedStatement preparedStatement = con.prepareStatement(sqlQuery, new String[]{"id"});
                    preparedStatement.setString(1, product.getOriginalId());
                    preparedStatement.setString(2, product.getName());
                    preparedStatement.setString(3, product.getNameTranslate());
                    preparedStatement.setString(4, product.getDescription());
                    preparedStatement.setDouble(5, product.getPrice());
                    preparedStatement.setDouble(6, product.getOldPrice() == null ? 0.0 : product.getOldPrice());
                    preparedStatement.setString(7, product.getShopName());
                    preparedStatement.setString(8, product.getImgUrl());
                    preparedStatement.setString(9, product.getBuyUrl());
                    preparedStatement.setBoolean(10, product.isAvailable());
                    return preparedStatement;
                }
            }, keyHolder);
            id = keyHolder.getKey().longValue();

        } catch (DuplicateKeyException dke) {
            id = update(product);
        } catch (DataIntegrityViolationException e) {
            logger.warning(e.getLocalizedMessage());
        }
        return id;
    }

    @Override
    public Long update(final Product product) {
        final String sqlQuery = " UPDATE product SET name = ?, translate_name  = ?, description  = ?, price  = ?, " +
                "old_price  = ?, shop_name  = ? , img_url  = ?, buy_url  = ?, available  = ?, modified = CURRENT_TIMESTAMP " +
                "WHERE original_id = ?";
        Long id = null;
        try {
            KeyHolder keyHolder = new GeneratedKeyHolder();
            jdbcTemplate.update(new PreparedStatementCreator() {
                @Override
                public PreparedStatement createPreparedStatement(Connection con) throws SQLException {
                    PreparedStatement preparedStatement = con.prepareStatement(sqlQuery, new String[]{"id"});
                    preparedStatement.setString(1, product.getName());
                    preparedStatement.setString(2, product.getNameTranslate());
                    preparedStatement.setString(3, product.getDescription());
                    preparedStatement.setDouble(4, product.getPrice());
                    preparedStatement.setDouble(5, product.getOldPrice());
                    preparedStatement.setString(6, product.getShopName());
                    preparedStatement.setString(7, product.getImgUrl());
                    preparedStatement.setString(8, product.getBuyUrl());
                    preparedStatement.setBoolean(9, product.isAvailable());
                    preparedStatement.setString(10, product.getOriginalId());
                    return preparedStatement;
                }
            });
            id = keyHolder.getKey() != null?keyHolder.getKey().longValue(): null;

        } catch (Exception e) {
            logger.info("Product with original_id " + product.getOriginalId() + " didn't update");
            e.printStackTrace();

        }
        return id;
    }

    @Override
    public Product getById(Long id) {
        String sqlQuery = "SELECT * FROM product WHERE id = ? ;";
        return jdbcTemplate.queryForObject(sqlQuery, new BeanPropertyRowMapper<Product>(Product.class), id.intValue());
    }

    @Override
    public Product getByOriginalId(String originalId) {
        String sqlQuery = "SELECT * FROM product WHERE original_id = ?;";
        return jdbcTemplate.queryForObject(sqlQuery, new BeanPropertyRowMapper<Product>(Product.class), originalId);
    }

    public List<Product> getAll(final int LIMIT ,final int OFFSET) {
        String sqlQuery = "SELECT * FROM product LIMIT ? OFFSET ?;";
        return jdbcTemplate.query(sqlQuery, new PreparedStatementSetter() {
            @Override
            public void setValues(PreparedStatement ps) throws SQLException {
                ps.setInt(1, LIMIT);
                ps.setInt(2, OFFSET);
            }
        }, new BeanPropertyRowMapper<Product>(Product.class));
    }

    @Override
    public List<Product> searchByParam( String param, final int limit, final int offset) {

        String sqlQuery = "SELECT * FROM product WHERE name ILIKE ? LIMIT ? OFFSET ?;";
        final String key = "%" + param + "%";
        return jdbcTemplate.query(sqlQuery, new PreparedStatementSetter() {
            @Override
            public void setValues(PreparedStatement ps) throws SQLException {
                ps.setString(1, key);
                ps.setInt(2, limit);
                ps.setInt(3, offset);
            }
        }, new BeanPropertyRowMapper<Product>(Product.class));
    }

    public Integer getCountByParam(String param){
        String sqlQuery = "SELECT count(id) FROM product WHERE ILIKE = ?;";
        final String key = "%" + param + "%";
        return jdbcTemplate.queryForObject(sqlQuery,new BeanPropertyRowMapper<Integer>(Integer.class),key);

    }

    public List<Product> searchByTag(final Tag tag, final int LIMIT, final int OFFSET) {
        final String sqlQuery = "SELECT p.* FROM product p " +
                "JOIN product_tags pt ON pt.product_id = p.id " +
                "JOIN tags AS t ON t.id = pt.tag_id " +
                "WHERE t.name ILIKE ?  ORDER BY p.price ASC LIMIT ? OFFSET  ?;";

        return jdbcTemplate.query(sqlQuery,new PreparedStatementSetter() {
            @Override
            public void setValues(PreparedStatement ps) throws SQLException {
                ps.setString(1, tag.getName());
                ps.setInt(2, LIMIT);
                ps.setInt(3, OFFSET);
            }
        }, new BeanPropertyRowMapper<Product>(Product.class));
    }
}
